package com.sihai.ehr.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

import javax.annotation.Resource;

import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Service;

import com.sihai.core.DataAccessor;
import com.sihai.core.DynamicQuery;
import com.sihai.core.Limit;
import com.sihai.core.LimitedList;
import com.sihai.core.QueryCreator;
import com.sihai.core.Sorter;
import com.sihai.core.support.HibernateDataAccessor;
import com.sihai.ehr.model.biz.ExamPeriod;
import com.sihai.ehr.model.biz.ExamStatistics;
import com.sihai.ehr.model.sys.ExaminePeriodEnum;
import com.sihai.ehr.model.sys.Organization;
import com.sihai.ehr.service.ExamStatisticsService;
import com.sihai.ehr.web.pojo.ExamStatisticsPojo;

/**
 * 员工考核统计服务实现
 * 
 * @author henry
 * 
 */
@Service("examStatisticsService")
public class ExamStatisticsServiceImpl implements ExamStatisticsService {

	@Resource
	DataAccessor dataAccessor;

	@Override
	public ExamStatistics getExamStatistics(Long id) {
		if (id == null || id < 0) {
			throw new RuntimeException("【传入参数非法】");
		}
		try {
			return dataAccessor.get(ExamStatistics.class, id);
		} catch (Exception e) {
			throw new RuntimeException("【异常原因：获取考核周期对象对象数据库发生异常】【异常说明：+"
					+ e.getMessage()
					+ "】【发生地点：ExamPeriodServiceImpl.getExamPeriod;参数id=" + id
					+ " 】");
		}
	}

	@Override
	public ExamStatistics getExamStatistics(final Long periodId,
			final Long workId) {
		if (periodId == null || periodId < 0 || workId == null || workId <= 0) {
			throw new RuntimeException("【传入参数非法】");
		}
		try {
			return dataAccessor.findUnique(ExamStatistics.class,
					new QueryCreator() {
						@Override
						public Query createQuery(Session session) {
							Query query = session
									.createQuery("from ExamStatistics e where e.examPeriod.id = :periodId and e.employeeinfoWork.id = :workId");
							query.setParameter("periodId", periodId);
							query.setParameter("workId", workId);
							return query;
						}
					});
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取考核周期对象对象数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamStatisticsServiceImpl.getExamStatistics;参数periodId="
							+ periodId + " ,workId:" + workId + "】");
		}
	}

	@Override
	public Float getExamPrevScore(final ExaminePeriodEnum periodEnum,
			final Long workId, final Long periodId) {
		if (periodEnum == null || workId <= 0) {
			throw new RuntimeException("【传入参数非法】");
		}
		try {
			Float scoreFloat = dataAccessor.findUnique(Float.class,
					new QueryCreator() {
						@Override
						public Query createQuery(Session session) {
							Query query = session
									.createQuery("select score from ExamStatistics e where e.examPeriod.periodType = :periodType"
											+ " and e.employeeinfoWork.id = :workId and e.examPeriod.id <> :periodId order by e.examPeriod.id desc limit 1");
							query.setParameter("periodType", periodEnum);
							query.setParameter("workId", workId);
							query.setParameter("periodId", periodId);
							return query;
						}
					});
			return scoreFloat == null ? 0 : scoreFloat;
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取定性考核员工上一期分数 数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamStatisticsServiceImpl.getExamPrevScore;参数periodEnum="
							+ periodEnum.toString() + ",workId=" + workId + "】");
		}
	}

	@Override
	public LimitedList<ExamStatistics> searchExamStatisticsByPeriod(
			final Long periodId, Limit limit, Sorter sorter) {
		if (periodId == null || periodId <= 0) {
			throw new RuntimeException("【传入参数非法】【periodId为空】");
		}
		try {
			return dataAccessor.findByQuery(ExamStatistics.class,
					new QueryCreator() {
						@Override
						public Query createQuery(Session session) {
							DynamicQuery dynamicQuery = new DynamicQuery(
									"from ExamStatistics e where 1<>2");
							dynamicQuery.appendIfNotNull(
									" and e.examPeriod.id = :periodId",
									"periodId", periodId);

							return dynamicQuery.build(session);
						}
					}, limit, sorter);
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取考核周期列表时数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamStatisticsServiceImpl.searchExamStatisticsByPeriod;参数periodId="
							+ periodId + "】");
		}
	}

	@Override
	public LimitedList<ExamStatistics> searchExamStatisticsByWork(
			final Long workId, Limit limit, Sorter sorter) {
		if (workId == null || workId <= 0) {
			throw new RuntimeException("【传入参数非法】【workId为空】");
		}
		try {
			return dataAccessor.findByQuery(ExamStatistics.class,
					new QueryCreator() {
						@Override
						public Query createQuery(Session session) {
							DynamicQuery dynamicQuery = new DynamicQuery(
									"from ExamStatistics e where 1<>2");
							dynamicQuery.appendIfNotNull(
									" and e.employeeinfoWork.id = :workId",
									"workId", workId);

							return dynamicQuery.build(session);
						}
					}, limit, sorter);
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取考核周期列表时数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamStatisticsServiceImpl.searchExamStatisticsByPeriod;参数workId="
							+ workId + "】");
		}
	}

	@Override
	public LimitedList<ExamStatisticsPojo> searchExamStatisticsPojos(
			int examType, final Long organizationId, final Long dutyId,
			final ExaminePeriodEnum periodEnum,
			final List<ExamPeriod> examPeriods, Limit limit, Sorter sorter) {
		LimitedList<ExamStatisticsPojo> examStatisticsPojos = new LimitedList<ExamStatisticsPojo>();
		Organization organization = null;
		boolean isReadTopOrg = false;
		String treePath = "";
		if (organizationId > 0) {
			try {
				organization = dataAccessor.get(Organization.class,
						organizationId);
				treePath = organization.getTreePath();
				if (organization.getTopOrganization() == null) {
					isReadTopOrg = true;
				}
			} catch (Exception e) {
				return null;
			}
		}
		String scoreStr = null;
		if (examType == 0) {
			scoreStr = "score";
		} else if (examType == 1) {
			scoreStr = "qual_score";
		} else if (examType == 2) {
			scoreStr = "quan_score";
		} else {
			return null;
		}

		int totalCount = 0;
		String strTotalString = "SELECT count(1)";
		strTotalString += " from employeeinfo_work as w,employeeinfo_base as e,organization as o,duty as d,grade as gr,grade as gt "
				+ "where w.base_id=e.id and w.organization_id=o.id and w.duty_id=d.id and w.grade_id=gr.id and w.lv_id=gt.id ";

		// 定义查询结果列表
		String strSQLString = "SELECT w.id as work_id,e.name,o.full_name,d.duty_name,gr.grade_name,gt.grade_sort,";
		strSQLString += "IFNULL((select "
				+ scoreStr
				+ " from exam_statistics as s1,exam_period as p1 where s1.period_id=p1.id and p1.exam_year="
				+ examPeriods.get(0).getExamYear() + " and p1.exam_monthly="
				+ examPeriods.get(0).getExamMonthly()
				+ " and s1.work_id=w.id and p1.period_type = '"
				+ periodEnum.toString() + "'), 0) AS 'early',";
		strSQLString += "IFNULL((select "
				+ scoreStr
				+ " from exam_statistics as s2,exam_period as p2 where s2.period_id=p2.id and p2.exam_year="
				+ examPeriods.get(1).getExamYear() + " and p2.exam_monthly="
				+ examPeriods.get(1).getExamMonthly()
				+ " and s2.work_id=w.id and p2.period_type = '"
				+ periodEnum.toString() + "'), 0) AS 'previous',";
		strSQLString += "IFNULL((select "
				+ scoreStr
				+ " from exam_statistics as s3,exam_period as p3 where s3.period_id=p3.id and p3.exam_year="
				+ examPeriods.get(2).getExamYear() + " and p3.exam_monthly="
				+ examPeriods.get(2).getExamMonthly()
				+ " and s3.work_id=w.id and p3.period_type = '"
				+ periodEnum.toString() + "'), 0) AS 'current'";
		strSQLString += " from employeeinfo_work as w,employeeinfo_base as e,organization as o,duty as d,grade as gr,grade as gt "
				+ "where w.base_id=e.id and w.organization_id=o.id and w.duty_id=d.id and w.grade_id=gr.id and w.lv_id=gt.id ";

		if (isReadTopOrg) {
			strTotalString += " and (o.top_organization_id=" + organizationId
					+ " or o.id=" + organizationId + ")";
			strSQLString += " and (o.top_organization_id=" + organizationId
					+ " or o.id=" + organizationId + ")";
		} else {
			// strTotalString += " and o.id=" + organizationId;
			// strSQLString += " and o.id=" + organizationId;
			strTotalString += " and o.tree_path like '" + treePath + "%'";
			strSQLString += " and o.tree_path like '" + treePath + "%'";
		}
		if (dutyId > 0) {
			strTotalString += " and d.id=" + dutyId;
			strSQLString += " and d.id=" + dutyId;
		}
        if(limit != null){
		    strSQLString += " limit " + limit.getOffset() + "," + limit.getCount();
        }
		Session vSession = ((HibernateDataAccessor) dataAccessor)
				.getHibernateTemplate().getSessionFactory().getCurrentSession();
		Connection vConnection = vSession.connection();
		Statement vStatement;
		try {
			vStatement = vConnection.createStatement();
			ResultSet rsTotal = vStatement.executeQuery(strTotalString);
			if (rsTotal.next()) {
				totalCount = rsTotal.getInt(1);
			}
			rsTotal.close();

			ResultSet rs = vStatement.executeQuery(strSQLString);
			while (rs.next()) {
				ExamStatisticsPojo statisticsPojo = new ExamStatisticsPojo(
						rs.getLong("work_id"), rs.getString("name"),
						rs.getString("full_name"), rs.getString("duty_name"),
						rs.getString("grade_name"), rs.getString("grade_sort"),
						rs.getFloat("early"), rs.getFloat("previous"),
						rs.getFloat("current"));
				examStatisticsPojos.add(statisticsPojo);
			}
			rs.close();
			vStatement.close();
			vConnection.close();
			examStatisticsPojos.setTotalcount(totalCount);
			return examStatisticsPojos;
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取定性考核打分对象集合时数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamScoreServiceImpl.searchExamScores;参数organizationId="
							+ organizationId + ",periodType=" + periodEnum
							+ "】");
		}
	}


    //进找用户个人的历史记录
    public LimitedList<ExamStatisticsPojo> searchExamStatisticsPojoForUser(int examType, final Long workId, final ExaminePeriodEnum periodEnum, final List<ExamPeriod> examPeriods) {
        LimitedList<ExamStatisticsPojo> examStatisticsPojos = new LimitedList<ExamStatisticsPojo>();
        String scoreStr = null;
        if (examType == 0) {
            scoreStr = "score";
        } else if (examType == 1) {
            scoreStr = "qual_score";
        } else if (examType == 2) {
            scoreStr = "quan_score";
        } else {
            return null;
        }

        int totalCount = 0;
        // 定义查询结果列表
        String strSQLString = "SELECT w.id as work_id,e.name,o.full_name,d.duty_name,gr.grade_name,gt.grade_sort,";
        strSQLString += "IFNULL((select "
                + scoreStr
                + " from exam_statistics as s1,exam_period as p1 where s1.period_id=p1.id and p1.exam_year="
                + examPeriods.get(0).getExamYear() + " and p1.exam_monthly="
                + examPeriods.get(0).getExamMonthly()
                + " and s1.work_id=w.id and p1.period_type = '"
                + periodEnum.toString() + "'), 0) AS 'early',";
        strSQLString += "IFNULL((select "
                + scoreStr
                + " from exam_statistics as s2,exam_period as p2 where s2.period_id=p2.id and p2.exam_year="
                + examPeriods.get(1).getExamYear() + " and p2.exam_monthly="
                + examPeriods.get(1).getExamMonthly()
                + " and s2.work_id=w.id and p2.period_type = '"
                + periodEnum.toString() + "'), 0) AS 'previous',";
        strSQLString += "IFNULL((select "
                + scoreStr
                + " from exam_statistics as s3,exam_period as p3 where s3.period_id=p3.id and p3.exam_year="
                + examPeriods.get(2).getExamYear() + " and p3.exam_monthly="
                + examPeriods.get(2).getExamMonthly()
                + " and s3.work_id=w.id and p3.period_type = '"
                + periodEnum.toString() + "'), 0) AS 'current'";
        strSQLString += " from employeeinfo_work as w,employeeinfo_base as e,organization as o,duty as d,grade as gr,grade as gt "
                + "where w.base_id=e.id and w.organization_id=o.id and w.duty_id=d.id and w.grade_id=gr.id and w.lv_id=gt.id and w.id="+workId;

        System.out.println("wxn-------2----------"+strSQLString);

        Session vSession = ((HibernateDataAccessor) dataAccessor)
                .getHibernateTemplate().getSessionFactory().getCurrentSession();
        Connection vConnection = vSession.connection();
        Statement vStatement;
        try {
            vStatement = vConnection.createStatement();
            ResultSet rs = vStatement.executeQuery(strSQLString);
            while (rs.next()) {
                ExamStatisticsPojo statisticsPojo = new ExamStatisticsPojo(
                        rs.getLong("work_id"), rs.getString("name"),
                        rs.getString("full_name"), rs.getString("duty_name"),
                        rs.getString("grade_name"), rs.getString("grade_sort"),
                        rs.getFloat("early"), rs.getFloat("previous"),
                        rs.getFloat("current"));
                examStatisticsPojos.add(statisticsPojo);
            }
            rs.close();
            vStatement.close();
            vConnection.close();
            examStatisticsPojos.setTotalcount(totalCount);
            return examStatisticsPojos;
        } catch (Exception e) {
            throw new RuntimeException("【异常原因：获取定性考核打分对象集合时数据库发生异常】");
        }
    }

	@Override
	public void saveExamStatistics(ExamStatistics examStatistics) {
		if (examStatistics == null) {
			throw new RuntimeException("【传入参数非法】【examStatistics为空】");
		}
		try {
			if (getExamStatistics(examStatistics.getExamPeriod().getId(),
					examStatistics.getEmployeeinfoWork().getId()) == null) {
				dataAccessor.saveOrUpdate(examStatistics);
			}

		} catch (Exception e) {
			throw new RuntimeException("【异常原因：保存考核统计到数据库发生异常】【异常说明："
					+ e.getMessage()
					+ "】【发生地点：ExamStatisticsServiceImpl.saveExamStatistics】");
		}
	}

	@Override
	public void deleteExamStatistics(final Long periodId, final Long workId) {
		if (periodId == null || periodId < 0 || workId == null || workId <= 0) {
			throw new RuntimeException("【传入参数非法】");
		}
		try {
			dataAccessor.batchUpdate(new QueryCreator() {

				@Override
				public Query createQuery(Session session) {
					return session
							.createQuery("delete from ExamStatistics e where e.examPeriod.id="
									+ periodId
									+ " and e.employeeinfoWork.id="
									+ workId);
				}
			});
		} catch (Exception e) {
			throw new RuntimeException("【异常原因：移出员工统计信息数据库发生异常】【异常说明："
					+ e.getMessage()
					+ "】【发生地点：ExamStatisticsServiceImpl.deleteExamStatistics】");
		}
	}

	// 查找指定员工指定周期类型的全年分数
	@Override
	public List<Object[]> findScoresByYear(Long workId,
			ExaminePeriodEnum periodEnum, int year) {
		return dataAccessor
				.findByHql(
						Object[].class,
						"select s.quanScore, s.examPeriod.periodName, s.examPeriod.id from ExamStatistics s where s.employeeinfoWork.id=? and s.examPeriod.periodType=? and s.examPeriod.examYear=? order by s.examPeriod.examMonthly",
						new Object[] { workId, periodEnum, year }, null, null);
	}

}
